#set( $controllerActions = ["List", "Create", "New", "Edit", "Update", "Detail", "Delete", "Index", "Help"])
/* Template $ThisTemplateName $Neo.Date by $Neo.User */
#foreach($entity in $Neo.Model.Entities)
#foreach($rel in $entity.ToOneRelationships)
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = object_id(N'fk_${entity.TableName}_${rel.VarName}') AND OBJECTPROPERTY(id, N'IsConstraint') = 1)
	ALTER TABLE $entity.TableName DROP CONSTRAINT fk_${entity.TableName}_${rel.VarName};

#end
#end

#foreach($entity in $Neo.Model.Entities)
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = object_id(N'$entity.TableName') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE $entity.TableName

#end

#foreach($entity in $Neo.Model.Entities)
CREATE TABLE $entity.TableName
(
#foreach($attribute in $entity.Attributes)
	$attribute.ColumnName $attribute.ColumnType#if($attribute.Size)($attribute.Size)#end#if($attribute.IsIdentityColumn && $entity.IdMethod.ToString == "Native") IDENTITY#end#if(!$attribute.AllowsNull) NOT NULL#end,
#end

	CONSTRAINT pk_$entity.TableName PRIMARY KEY ( #foreach($column in $entity.PkColumns)#if( $velocityCount > 1), #end$column.ColumnName#end )
);

#end

#foreach($entity in $Neo.Model.Entities)
#if($entity.ToOneRelationships.Count > 0)
#foreach($rel in $entity.ToOneRelationships)
ALTER TABLE $entity.TableName ADD CONSTRAINT fk_${entity.TableName}_${rel.VarName} FOREIGN KEY ( $rel.LocalKey ) REFERENCES $rel.ForeignEntity.TableName ( $rel.ForeignKey ); 
#end

#end
#end

#set ($useBuiltins = 0)
#foreach($entity in $Neo.Model.Entities)
#if ($entity.TableName.EndsWith("Security_RoleObjectPrivledgeLinks"))
#set ($useBuiltins = 1)
#end
#end

#if ($Neo.UseBuiltins)
insert into ${Neo.Model.TablePrefix}Security_Users (UserID, FirstName, LastName, Login, Password, DateCreated, Active) values ( NEWID(), '', '', 'admin', 'password', GetDate(), 1);
insert into ${Neo.Model.TablePrefix}Security_Users (UserID, FirstName, LastName, Login, Password, DateCreated, Active) values ( NEWID(), '', '', 'Anonymous', '', GetDate(), 1);

insert into ${Neo.Model.TablePrefix}Security_Roles (RoleID, Name) values (NEWID(), 'Administrator');
insert into ${Neo.Model.TablePrefix}Security_Roles (RoleID, Name) values (NEWID(), 'User');

insert into ${Neo.Model.TablePrefix}Security_UserRoleLinks (UserRoleID, UserID, RoleID) select NEWID(), ${Neo.Model.TablePrefix}Security_Users.UserID, ${Neo.Model.TablePrefix}Security_Roles.RoleID from ${Neo.Model.TablePrefix}Security_Users, ${Neo.Model.TablePrefix}Security_Roles where ${Neo.Model.TablePrefix}Security_Users.Login = 'admin' and ${Neo.Model.TablePrefix}Security_Roles.Name = 'Administrator';
insert into ${Neo.Model.TablePrefix}Security_UserRoleLinks (UserRoleID, UserID, RoleID) select NEWID(), ${Neo.Model.TablePrefix}Security_Users.UserID, ${Neo.Model.TablePrefix}Security_Roles.RoleID from ${Neo.Model.TablePrefix}Security_Users, ${Neo.Model.TablePrefix}Security_Roles where ${Neo.Model.TablePrefix}Security_Users.Login = 'Anonymous' and ${Neo.Model.TablePrefix}Security_Roles.Name = 'User';

#foreach($action in $controllerActions)
insert into ${Neo.Model.TablePrefix}Security_Privledges (PrivledgeID, Name) values(NEWID(), '${action}');
#end

#foreach($entity in $Neo.Model.Entities)
insert into ${Neo.Model.TablePrefix}Security_Objects (ObjectID, Name) values (NEWID(), '${entity.ClassName}');

insert into ${Neo.Model.TablePrefix}Security_RoleObjectLinks (RoleObjectID, RoleID, ObjectID)  select NEWID(), ${Neo.Model.TablePrefix}Security_Roles.RoleID, ${Neo.Model.TablePrefix}Security_Objects.ObjectID from ${Neo.Model.TablePrefix}Security_Roles, ${Neo.Model.TablePrefix}Security_Objects where ${Neo.Model.TablePrefix}Security_Roles.Name = 'Administrator' and ${Neo.Model.TablePrefix}Security_Objects.Name = '${entity.ClassName}';

#foreach($action in $controllerActions)
insert into ${Neo.Model.TablePrefix}Security_RoleObjectPrivledgeLinks (RoleObjectPrivledgeID, RoleObjectID, PrivledgeID, Enabled) select NEWID(), ${Neo.Model.TablePrefix}Security_RoleObjectLinks.RoleObjectID, ${Neo.Model.TablePrefix}Security_Privledges.PrivledgeID, 1 from ${Neo.Model.TablePrefix}Security_Roles, ${Neo.Model.TablePrefix}Security_Objects, ${Neo.Model.TablePrefix}Security_RoleObjectLinks, ${Neo.Model.TablePrefix}Security_Privledges where ((${Neo.Model.TablePrefix}Security_RoleObjectLinks.RoleID = ${Neo.Model.TablePrefix}Security_Roles.RoleID and ${Neo.Model.TablePrefix}Security_Roles.Name = 'Administrator') and (${Neo.Model.TablePrefix}Security_RoleObjectLinks.ObjectID = ${Neo.Model.TablePrefix}Security_Objects.ObjectID and ${Neo.Model.TablePrefix}Security_Objects.Name = '${entity.ClassName}')) and ${Neo.Model.TablePrefix}Security_Privledges.Name = '${action}';
#end

#end

#foreach($entity in $Neo.Model.Entities)

insert into ${Neo.Model.TablePrefix}Security_RoleObjectLinks (RoleObjectID, RoleID, ObjectID)  select NEWID(), ${Neo.Model.TablePrefix}Security_Roles.RoleID, ${Neo.Model.TablePrefix}Security_Objects.ObjectID from ${Neo.Model.TablePrefix}Security_Roles, ${Neo.Model.TablePrefix}Security_Objects where ${Neo.Model.TablePrefix}Security_Roles.Name = 'User' and ${Neo.Model.TablePrefix}Security_Objects.Name = '${entity.ClassName}';

insert into ${Neo.Model.TablePrefix}Security_RoleObjectPrivledgeLinks (RoleObjectPrivledgeID, RoleObjectID, PrivledgeID, Enabled) select NEWID(), ${Neo.Model.TablePrefix}Security_RoleObjectLinks.RoleObjectID, ${Neo.Model.TablePrefix}Security_Privledges.PrivledgeID, 1 from ${Neo.Model.TablePrefix}Security_Roles, ${Neo.Model.TablePrefix}Security_Objects, ${Neo.Model.TablePrefix}Security_RoleObjectLinks, ${Neo.Model.TablePrefix}Security_Privledges where ((${Neo.Model.TablePrefix}Security_RoleObjectLinks.RoleID = ${Neo.Model.TablePrefix}Security_Roles.RoleID and ${Neo.Model.TablePrefix}Security_Roles.Name = 'User') and (${Neo.Model.TablePrefix}Security_RoleObjectLinks.ObjectID = ${Neo.Model.TablePrefix}Security_Objects.ObjectID and ${Neo.Model.TablePrefix}Security_Objects.Name = '${entity.ClassName}')) and ${Neo.Model.TablePrefix}Security_Privledges.Name = 'Index';

#end

insert into ${Neo.Model.TablePrefix}Security_Objects (ObjectID, Name) values (NEWID(), 'FileHandler');

insert into ${Neo.Model.TablePrefix}Security_RoleObjectLinks (RoleObjectID, RoleID, ObjectID)  select NEWID(), ${Neo.Model.TablePrefix}Security_Roles.RoleID, ${Neo.Model.TablePrefix}Security_Objects.ObjectID from ${Neo.Model.TablePrefix}Security_Roles, ${Neo.Model.TablePrefix}Security_Objects where ${Neo.Model.TablePrefix}Security_Roles.Name = 'Administrator' and ${Neo.Model.TablePrefix}Security_Objects.Name = 'FileHandler';

#foreach($action in $controllerActions)
insert into ${Neo.Model.TablePrefix}Security_RoleObjectPrivledgeLinks (RoleObjectPrivledgeID, RoleObjectID, PrivledgeID, Enabled) select NEWID(), ${Neo.Model.TablePrefix}Security_RoleObjectLinks.RoleObjectID, ${Neo.Model.TablePrefix}Security_Privledges.PrivledgeID, 1 from ${Neo.Model.TablePrefix}Security_Roles, ${Neo.Model.TablePrefix}Security_Objects, ${Neo.Model.TablePrefix}Security_RoleObjectLinks, ${Neo.Model.TablePrefix}Security_Privledges where ((${Neo.Model.TablePrefix}Security_RoleObjectLinks.RoleID = ${Neo.Model.TablePrefix}Security_Roles.RoleID and ${Neo.Model.TablePrefix}Security_Roles.Name = 'Administrator') and (${Neo.Model.TablePrefix}Security_RoleObjectLinks.ObjectID = ${Neo.Model.TablePrefix}Security_Objects.ObjectID and ${Neo.Model.TablePrefix}Security_Objects.Name = 'FileHandler')) and ${Neo.Model.TablePrefix}Security_Privledges.Name = '${action}';
#end

insert into ${Neo.Model.TablePrefix}Security_Objects (ObjectID, Name) values (NEWID(), 'Home');

insert into ${Neo.Model.TablePrefix}Security_RoleObjectLinks (RoleObjectID, RoleID, ObjectID)  select NEWID(), ${Neo.Model.TablePrefix}Security_Roles.RoleID, ${Neo.Model.TablePrefix}Security_Objects.ObjectID from ${Neo.Model.TablePrefix}Security_Roles, ${Neo.Model.TablePrefix}Security_Objects where ${Neo.Model.TablePrefix}Security_Roles.Name = 'Administrator' and ${Neo.Model.TablePrefix}Security_Objects.Name = 'Home';

#foreach($action in $controllerActions)
insert into ${Neo.Model.TablePrefix}Security_RoleObjectPrivledgeLinks (RoleObjectPrivledgeID, RoleObjectID, PrivledgeID, Enabled) select NEWID(), ${Neo.Model.TablePrefix}Security_RoleObjectLinks.RoleObjectID, ${Neo.Model.TablePrefix}Security_Privledges.PrivledgeID, 1 from ${Neo.Model.TablePrefix}Security_Roles, ${Neo.Model.TablePrefix}Security_Objects, ${Neo.Model.TablePrefix}Security_RoleObjectLinks, ${Neo.Model.TablePrefix}Security_Privledges where ((${Neo.Model.TablePrefix}Security_RoleObjectLinks.RoleID = ${Neo.Model.TablePrefix}Security_Roles.RoleID and ${Neo.Model.TablePrefix}Security_Roles.Name = 'Administrator') and (${Neo.Model.TablePrefix}Security_RoleObjectLinks.ObjectID = ${Neo.Model.TablePrefix}Security_Objects.ObjectID and ${Neo.Model.TablePrefix}Security_Objects.Name = 'Home')) and ${Neo.Model.TablePrefix}Security_Privledges.Name = '${action}';
#end

insert into ${Neo.Model.TablePrefix}Security_RoleObjectLinks (RoleObjectID, RoleID, ObjectID)  select NEWID(), ${Neo.Model.TablePrefix}Security_Roles.RoleID, ${Neo.Model.TablePrefix}Security_Objects.ObjectID from ${Neo.Model.TablePrefix}Security_Roles, ${Neo.Model.TablePrefix}Security_Objects where ${Neo.Model.TablePrefix}Security_Roles.Name = 'User' and ${Neo.Model.TablePrefix}Security_Objects.Name = 'Home';

insert into ${Neo.Model.TablePrefix}Security_RoleObjectPrivledgeLinks (RoleObjectPrivledgeID, RoleObjectID, PrivledgeID, Enabled) select NEWID(), ${Neo.Model.TablePrefix}Security_RoleObjectLinks.RoleObjectID, ${Neo.Model.TablePrefix}Security_Privledges.PrivledgeID, 1 from ${Neo.Model.TablePrefix}Security_Roles, ${Neo.Model.TablePrefix}Security_Objects, ${Neo.Model.TablePrefix}Security_RoleObjectLinks, ${Neo.Model.TablePrefix}Security_Privledges where ((${Neo.Model.TablePrefix}Security_RoleObjectLinks.RoleID = ${Neo.Model.TablePrefix}Security_Roles.RoleID and ${Neo.Model.TablePrefix}Security_Roles.Name = 'User') and (${Neo.Model.TablePrefix}Security_RoleObjectLinks.ObjectID = ${Neo.Model.TablePrefix}Security_Objects.ObjectID and ${Neo.Model.TablePrefix}Security_Objects.Name = 'Home')) and ${Neo.Model.TablePrefix}Security_Privledges.Name = 'Index';

#end